package org.compiere.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.DialogAgreement;
import org.compiere.apps.IProcessParameter;
import org.compiere.apps.ProcessCtl;
import org.compiere.apps.ProcessParameterPanel;
import org.compiere.model.I_BPM_Form;
import org.compiere.model.I_BPM_Project;
import org.compiere.model.MBPMForm;
import org.compiere.model.MBPMFormCell;
import org.compiere.model.MBPMFormColumn;
import org.compiere.model.MBPMFormLine;
import org.compiere.model.MBPMFormValues;
import org.compiere.model.MBPMParameters;
import org.compiere.model.MBPMProject;
import org.compiere.model.MBPMSubs;
import org.compiere.model.MFormula;
import org.compiere.model.MParameter;
import org.compiere.model.MParameterLine;
import org.compiere.model.MTable;
import org.compiere.model.MVariable;
import org.compiere.model.X_BPM_FormCell;
import org.compiere.model.X_BPM_FormColumn;
import org.compiere.model.X_BPM_FormLine;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.compiere.util.Trx;

import extend.org.compiere.utils.Util;

public class BPM_CalcFormValues extends SvrProcess {

	/* Current context			*/
	private Properties m_ctx;
	/*	Optional Transaction 	*/
	private String	m_trxName = null;
	/* */
	private int BPM_Form_ID = 0;
	/* */
	private int BPM_Subs_ID = 0;
	/* */
	public int BPM_Project_ID = MBPMProject.TempProjectID;  
	/* */
	private MBPMForm form = null;
	private MBPMFormLine formLine[] = null;
	private MBPMFormCell formCell[] = null;
	/* */
	private int AD_Process_ID;
	
	@Override
	protected void prepare() {

		m_ctx = getCtx();
		m_trxName = get_TrxName();
		BPM_Form_ID = getRecord_ID();
		
		BPM_Subs_ID = MBPMSubs.getSubsID(Env.getAD_User_ID(Env.getCtx()));
		if(BPM_Subs_ID == 0)
			DialogAgreement.dialogOK("Орг. единицы", "Не найдена головная организационная единица", 0);
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo(I_BPM_Form.COLUMNNAME_BPM_Form_ID) == 0 && para[i].getParameter() != null)
				BPM_Form_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo(I_BPM_Project.COLUMNNAME_BPM_Project_ID) == 0 && para[i].getParameter() != null)
				BPM_Project_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		form = new MBPMForm(m_ctx, BPM_Form_ID, m_trxName);
		formLine = MBPMForm.getLines(m_ctx, form.getBPM_Form_ID(), m_trxName);
		

	}

	@Override
	protected String doIt() throws Exception {
		
		for(MBPMFormLine l: formLine){
			
			int BSC_Parameter_ID = 0;
			
			formCell = MBPMFormLine.getLines(m_ctx, l.getBPM_FormLine_ID(), m_trxName);
			for(MBPMFormCell cell: formCell){
				
				// Получим показатель ячейки
				BSC_Parameter_ID = cell.getBSC_Parameter_ID();
				MParameter pCell = new MParameter(m_ctx, BSC_Parameter_ID, get_TrxName());
				pCell.getZeroParameterLine().setValue("0");
				pCell.saveEx(); // clear
				
				// устанавливаем приоритет. 1. Ячейка, 2. Строка, 3. Колонка
				if(!cell.isParameter()) {
					if(l.isParameter() && !cell.getBPM_FormColumn().isHead()) {
						BSC_Parameter_ID = l.getBSC_Parameter_ID();
					}
					else if(cell.getBPM_FormColumn().isParameter()) {
						BSC_Parameter_ID = cell.getBPM_FormColumn().getBSC_Parameter_ID();
					}
				}
				
				MBPMFormValues value = MBPMFormValues.getFormValueLine(l.getBPM_FormLine_ID(), cell.getBPM_FormColumn_ID(), form.getBPM_VersionBudget_ID(), BPM_Project_ID);
				if(value == null)
					value = new MBPMFormValues(m_ctx, 0, m_trxName);
				value.setBPM_Form_ID(l.getBPM_Form_ID());
				value.setBPM_FormLine_ID(l.getBPM_FormLine_ID());
				value.setBPM_FormColumn_ID(cell.getBPM_FormColumn_ID());
				value.setBPM_VersionBudget_ID(form.getBPM_VersionBudget_ID());
				value.setBPM_Project_ID(BPM_Project_ID);
				value.setBPM_FormCell_ID(cell.get_ID());
				value.setBPM_Subs_ID(BPM_Subs_ID);
				
				if(cell.getCellValue().doubleValue() == 0){
					
					LinkedHashMap<String, Object> obj = new LinkedHashMap<String, Object>();
					getParameters(MTable.getTable_ID(X_BPM_FormColumn.Table_Name), cell.getBPM_FormColumn_ID(), obj);
					getParameters(MTable.getTable_ID(X_BPM_FormLine.Table_Name), cell.getBPM_FormLine_ID(), obj);
					getParameters(MTable.getTable_ID(X_BPM_FormCell.Table_Name), cell.get_ID(), obj);
					value.setCellValue("");
					value.setAlternateValue("");
					
					MParameterLine parLine = getLineParameter(BSC_Parameter_ID);
					
					//
					String result = "0";
					if(parLine.isFormula()){
						HashMap<String,Object> args = new HashMap<String,Object>(); 
						Map<String, MVariable> map = parLine.getVariables();
						for(String key: map.keySet()){
							MVariable var = map.get(key);  
							if(var.getBSC_Parameter_ID() > 0) {
								
								obj = new LinkedHashMap<String, Object>();
								int BPM_FormColumn_ID = 0;
								int BPM_FormLine_ID = 0;
								
								if (cell.getBPM_FormColumn().isParameter()) {
									BPM_FormLine_ID = cell.getBPM_FormLine_ID();
									MBPMFormColumn[] fCol = MBPMFormColumn.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), get_TrxName());
									for(MBPMFormColumn cl: fCol){
										getParameters(MTable.getTable_ID(X_BPM_FormColumn.Table_Name), cl.getBPM_FormColumn_ID(), obj);
									}
									BPM_FormColumn_ID = (fCol.length > 0 ? fCol[0].getBPM_FormColumn_ID():0);
								}  
								if (cell.getBPM_FormLine().isParameter() && !cell.getBPM_FormColumn().isHead()) {
									BPM_FormColumn_ID = cell.getBPM_FormColumn_ID();
									MBPMFormLine[] fLine = MBPMFormLine.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), m_trxName);
									
									for(MBPMFormLine cl: fLine){
										getParameters(MTable.getTable_ID(X_BPM_FormLine.Table_Name), cl.getBPM_FormLine_ID(), obj);
									}
									BPM_FormLine_ID = (fLine.length > 0 ? fLine[0].getBPM_FormLine_ID():0);
								}
								if (cell.isParameter()) {
									int _BSC_Parameter_ID = var.getBSC_Parameter_ID();
									MBPMFormCell[] _cc = MBPMFormCell.getLinesParameter(m_ctx, _BSC_Parameter_ID, get_TrxName());
									if (_cc.length > 0) {
										BPM_FormColumn_ID = _cc[0].getBPM_FormColumn_ID();
										BPM_FormLine_ID = _cc[0].getBPM_FormLine_ID();
									}
								}
								
								MBPMFormCell[] varCell = MBPMFormCell.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), get_TrxName());
								MBPMFormLine[] varLine = MBPMFormLine.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), get_TrxName());
								MBPMFormColumn[] varCol = MBPMFormColumn.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), get_TrxName());
								if (varCell.length > 0) {
									if (MBPMFormCell.getBPM_Form_ID(varCell[0].getBPM_FormCell_ID()) != BPM_Form_ID){
										BPM_FormColumn_ID = varCell[0].getBPM_FormColumn_ID();
										BPM_FormLine_ID = varCell[0].getBPM_FormLine_ID();
									}
								} else if (varLine.length > 0 && varLine[0].getBPM_Form_ID() != BPM_Form_ID) {
									throw new AdempiereException("Нельзя однозначно определить ячейку другой формы. Параметер формулы {"+key+"} ссылается на линию из другой формы. Ошибка расчетов в линии \""+l.getName()+"\"");
								} else if (varCol.length > 0 && MBPMFormColumn.getBPM_Form_ID(varCol[0].getBPM_FormColumn_ID()) != BPM_Form_ID) {
									MBPMFormColumn cl = new MBPMFormColumn(m_ctx, BPM_FormColumn_ID, get_TrxName());
									throw new AdempiereException("Нельзя однозначно определить ячейку другой формы. Параметер формулы {"+key+"} ссылается на колонку из другой формы. Ошибка расчетов в колонке \""+cl.getName()+"\"");
								}
								
								MBPMFormCell cc  = MBPMFormCell.getCell(m_ctx, BPM_FormColumn_ID, BPM_FormLine_ID, get_TrxName());
								
								if (BPM_FormLine_ID > 0 && BPM_FormColumn_ID > 0) {
									getParameters(MTable.getTable_ID(X_BPM_FormColumn.Table_Name), cc.getBPM_FormColumn_ID(), obj);
									getParameters(MTable.getTable_ID(X_BPM_FormLine.Table_Name), cc.getBPM_FormLine_ID(), obj);
									getParameters(MTable.getTable_ID(X_BPM_FormCell.Table_Name), cc.get_ID(), obj);
								}

								
								if(!cc.isParameter() && (var.getParameter().getZeroParameterLine().isImported())) {
									MBPMFormCell[] cTemp = MBPMFormCell.getLinesParameter(m_ctx, var.getBSC_Parameter_ID(), get_TrxName());
									
									if (cTemp.length > 0) {
										cc = (cTemp.length> 0? cTemp[0] : null);
										getParameters(MTable.getTable_ID(X_BPM_FormColumn.Table_Name), cc.getBPM_FormColumn_ID(), obj);
										getParameters(MTable.getTable_ID(X_BPM_FormLine.Table_Name), cc.getBPM_FormLine_ID(), obj);
										getParameters(MTable.getTable_ID(X_BPM_FormCell.Table_Name), cc.get_ID(), obj);
									} else {
										getParameters(MTable.getTable_ID(X_BPM_FormColumn.Table_Name), cell.getBPM_FormColumn_ID(), obj);
									}
									String value1 = var.getParameter().getZeroParameterLine().calculate(obj);
									args.put(key, value1);
								} else {
									MParameter p = new MParameter(m_ctx, cc.getBSC_Parameter_ID(),get_TrxName());
									String value1 = p.getZeroParameterLine().getValue(obj);
									args.put(key, value1);
								}

								MFormula f = parLine.getFormula();
								result = MFormula.calc(f.getFormula(), args);
							}
						}
					}else{
						result = parLine.calculate(obj);
					}
					
					MParameter pp = new MParameter(m_ctx, cell.getBSC_Parameter_ID(), get_TrxName());
					pp.getZeroParameterLine().setValue(result);
					pp.getZeroParameterLine().saveEx();
					
					try{
						value.setCellValue(result);
					}catch(Exception ex){
						value.setAlternateValue(result);
					}
					finally{
						value.saveEx();
					}
				}else{
					pCell.getZeroParameterLine().setValue(String.valueOf(cell.getCellValue()));
					pCell.getZeroParameterLine().saveEx();
					cell.setisParameter(true);
					cell.saveEx();
					
					value.setCellValue(cell.getCellValue().toString());
					value.saveEx();
				}
			}
		}
		
		// 
		Trx trx = Trx.get(m_trxName, false);
		trx.commit();
		trx.close();
		
		// Run Excel
		if(BPM_Project_ID == MBPMProject.TempProjectID)
			return runExcel(BPM_Form_ID, BPM_Project_ID);
		else
			return Msg.translate(m_ctx, "Success");
	}
	
	private MParameterLine getLineParameter(int BSC_Parameter_ID){
		
		CLogger log = CLogger.getCLogger (MParameterLine.class);
		MParameterLine result = new MParameterLine(m_ctx, 0, m_trxName);
		String sql = "SELECT * FROM BSC_ParameterLine WHERE isActive = 'Y' AND BSC_Parameter_ID = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try {
			pstmt = DB.prepareStatement(sql,null);
			pstmt.setInt (1, BSC_Parameter_ID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = new MParameterLine(Env.getCtx(),rs,null);
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "MParameterLine: ", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
	
		return result;
	}
	
	private String runExcel(int BPM_Form_ID, int BPM_Project_ID){
		
		String nameProcess = DSR_GenerateReport.class.getName();		
		
		// org.compiere.process.DSR_GenerateReport
		AD_Process_ID = Util.getAD_Process(nameProcess.substring(nameProcess.lastIndexOf(".")+1, nameProcess.length()));
		
		if(AD_Process_ID == 0)
			return  "The process can not be found";
		ProcessInfo pi = new ProcessInfo ("The run Excel form values", AD_Process_ID);
		//  Prepare Process
		pi.setAD_User_ID (Env.getAD_User_ID(m_ctx));
		pi.setAD_Client_ID(Env.getAD_Client_ID(m_ctx));

		List<ProcessInfoParameter> po = new ArrayList<ProcessInfoParameter>();
		po.add(new ProcessInfoParameter(I_BPM_Form.COLUMNNAME_BPM_Form_ID, BPM_Form_ID,null,"",""));
		po.add(new ProcessInfoParameter(I_BPM_Project.COLUMNNAME_BPM_Project_ID, BPM_Project_ID,null,"",""));
		ProcessInfoParameter[] pp = new ProcessInfoParameter[po.size()];
		po.toArray(pp);
		pi.setParameter(pp);
		//	Execute Process
		ProcessParameterPanel pu = new ProcessParameterPanel(0, pi);
		ProcessCtl.process(null, 0, (IProcessParameter) pu, pi, null);
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private LinkedHashMap<String, Object> getParameters(int AD_Table_ID, int Record_ID, LinkedHashMap<String, Object> param){
		
		MBPMParameters[] parameters = MBPMParameters.getLines(m_ctx, AD_Table_ID, Record_ID, m_trxName);
		
		String in = "";
		int counter = 0;
		boolean isChanged = false;
		
		String type = parameters.length > 0 ? parameters[0].getColumnName() : "";
		
		for(MBPMParameters par: parameters){
			
			isChanged = !type.equals(par.getColumnName());
			
			if(!isChanged)
			{
				if(counter > 0)
					in += ", ";
				
				counter++;
			}
			else
			{
				counter = 1;
				in = "";
			}
			in += par.getValue1();
			
			if(par.getOperation().trim().toLowerCase().equals("between") && par.getValue2() != null && !par.getValue2().isEmpty())
				in += " AND ".concat(par.getValue2());
			
			param.put(par.getColumnName(), in);
			
			type = par.getColumnName();
		}
		
		if(BPM_Project_ID != MBPMProject.TempProjectID && BPM_Project_ID != 0)
			param.put(I_BPM_Project.COLUMNNAME_BPM_Project_ID, String.valueOf(BPM_Project_ID));
		
		return param;
	}

}
